How to: Create a field that finds and displays values from another field.
Solution:
In the Table Design view, insert the field, and type a name for the field. Then select 'Lookup Wizard...' from the drop-down list in the 'Data Type' column, and follow the instructions.
1) If the Database window is not active, activate the Database window.
2) Go to the Table Design view.
3) To insert a field, click in the next blank row.
4) Type a name for the field in the 'Field Name' column.
5) Click in the 'Data Type' column. (A down arrow appears.)
6) Click on the arrow in the 'Data Type' column. (A drop-down list appears.)
7) Select 'Lookup Wizard..' from the drop-down list. (The Lookup Wizard dialog box appears.)
8) Select the 'I want the lookup column to look up the values in a table or query.' radio button.
9) Click 'Next'.
10) Select the table or query from which to get the values from the list box.
NOTE: To select from a list of only tables, select the 'Tables' radio button in the 'View' group. To select from a list of only queries, select the 'Queries' radio button. To select from a list of both tables and queries, select the 'Both' radio button.
11) Click 'Next'.
12) Select the field(s) containing the values to include in the lookup table:
a) Select a field from the 'Available fields' list box.
b) Choose the '>' button. (The selected field appears in the Selected fields list box.)
c) Repeat steps 12)a) and 12)b) for each field containing values to include in the lookup table.
13) Click 'Next'.
14) Adjust the width of the column as desired.
15) Click 'Next'.
16) Type a label for the lookup column in the 'What label would you like for your lookup column?' box.
17) Choose the 'Finish' button.
18) (Optional) Make any changes to the field properties:
a) Move to the 'Field Properties' section.
Field Properties section
b) Choose the 'General' tab.
c) Make the desired entries and selections.
d) Choose the 'Lookup' tab.
e) Make the desired entries and selections.
19) Select the 'File' menu and select 'Save' to save changes.
20) Select the 'File' menu and select 'Close' to close the Table Design view.